package com.mk.common.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import java.awt.Color;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * @Author: yq
 * @Description:
 * @Date: Created in 4:37 PM 3/20/18
 */
public class ExcelExportUtil {

    public static void exportExcel(OutputStream out, ExcelData data) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();
        String sheetName = data.getSheetName();
        if(null == sheetName)
            sheetName = "Sheet1";

        XSSFSheet sheet = wb.createSheet(sheetName);
        writeExcel(wb,sheet,data);

        wb.write(out);
    }

    private static void writeExcel(XSSFWorkbook wb, XSSFSheet sheet, ExcelData data) {

        int rowIndex = 0;

        rowIndex = writeTitlesToExcel(wb,sheet,data.getTitles());
        writeRowsToExcel(wb,sheet,data.getRows(),rowIndex,data.getColumns());
        autoSizeColumns(sheet,data.getTitles().size() + 1);

    }

    private static int writeTitlesToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;

        Font titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
//        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
//        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0,0,0)));

        Row titleRow = sheet.createRow(rowIndex);
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }

        rowIndex++;
        return rowIndex;
    }

    private static int writeRowsToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<Map<String, Object>> rows, int rowIndex,List<String> columns) {
        int colIndex = 0;
        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle datastyle = wb.createCellStyle();
//        datastyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//        datastyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        datastyle.setFont(dataFont);
        datastyle.setWrapText(true);//设置自动换行
        setBorder(datastyle, BorderStyle.THIN, new XSSFColor(new Color(0,0,0)));

        for (Map<String, Object> row : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            colIndex = 0;

            for(String column : columns){
                Cell cell = dataRow.createCell(colIndex);
                if(null != row.get(column))
                    cell.setCellValue(row.get(column).toString());
                else
                    cell.setCellValue("");

                cell.setCellStyle(datastyle);
                colIndex++;
            }

            rowIndex++;
        }
        return rowIndex;
    }

    private static void autoSizeColumns(XSSFSheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i,true);
            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
            if(newWidth > orgWidth)
                sheet.setColumnWidth(i,newWidth);
            else
                sheet.setColumnWidth(i,orgWidth);
        }

    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color){
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT,color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM,color);
    }
}
